import openpyxl
from openpyxl.styles import Alignment

book = openpyxl.load_workbook('data/netshop.xlsx')


def query(sheet: str, col: int, val: any) -> [dict]:
    """
    根据某一列查询对象
    :param sheet: 表
    :param col: 列数
    :param val: 值
    :return: 符合的行
    """
    global book
    sheet = book[sheet]
    keys = [cell.value for cell in tuple(sheet.rows)[0]]
    result = []
    for row, cell in enumerate(tuple(sheet.columns)[col][1:], 1):
        if cell.value == val:
            cval = [cell.value for cell in tuple(sheet.rows)[row]]
            res = dict(zip(keys, cval))
            res['_row'] = row
            res['_val'] = cval
            result.append(res)
    return result


def query_not(sheet: str, col: int, val: any) -> [dict]:
    """
    根据某一列查询对象
    :param sheet: 表
    :param col: 列数
    :param val: 值
    :return: 符合的行
    """
    global book
    sheet = book[sheet]
    keys = [cell.value for cell in tuple(sheet.rows)[0]]
    result = []
    for row, cell in enumerate(tuple(sheet.columns)[col][1:], 1):
        if cell.value != val:
            cval = [cell.value for cell in tuple(sheet.rows)[row]]
            res = dict(zip(keys, cval))
            res['_row'] = row
            res['_val'] = cval
            result.append(res)
    return result


def query_contains(sheet: str, col: int, key: str) -> [dict]:
    """
    根据某一列模糊查询对象
    :param sheet: 表
    :param col: 列数
    :param key: 关键字
    :return: 符合的行
    """
    global book
    sheet = book[sheet]
    keys = [cell.value for cell in tuple(sheet.rows)[0]]
    result = []
    for row, cell in enumerate(tuple(sheet.columns)[col][1:], 1):
        if key in cell.value:
            val = [cell.value for cell in tuple(sheet.rows)[row]]
            res = dict(zip(keys, val))
            res['_row'] = row
            res['_val'] = val
            result.append(res)
    return result


def query_dict(sheet: str, key_col: int, val_col: int) -> dict:
    """
    查询两列，并将其作为一个表
    :param val_col: 作为值的列
    :param key_col: 作为键的列
    :param sheet: 表
    :return: 符合的行
    """
    global book
    sheet = book[sheet]
    list_key = [cell.value for cell in tuple(sheet.columns)[key_col][1:]]
    list_val = [cell.value for cell in tuple(sheet.columns)[val_col][1:]]
    return dict(zip(list_key, list_val))


def query_first(sheet: str, col: int, val: any, *args) -> dict | None:
    """
    只查找一行
    :param sheet: 表
    :param col: 列数
    :param val: 值
    :param args: 待查找的其他 行 - 值 对
    :return: 符合的行
    """
    global book
    sheet = book[sheet]
    keys = [cell.value for cell in tuple(sheet.rows)[0]]
    for row, cell in enumerate(tuple(sheet.columns)[col][1:], 1):
        if cell.value == val:
            cval = [cell.value for cell in tuple(sheet.rows)[row]]
            accept = True
            for v in args:
                if cval[v[0]] != v[1]:
                    accept = False
                    break
            if not accept:
                continue
            res = dict(zip(keys, cval))
            res['_row'] = row
            res['_val'] = cval
            return res
    return None


def update_cells(sheet: str, values: dict):
    """
    更新某一单元格
    :param values: 数据
    :param sheet: 表
    :return: None
    """
    global book
    sheet = book[sheet]
    if 'alignments' in values.keys() and values['alignments'] is not None:
        for addr in values['alignments']:
            sheet[addr].alignment = Alignment(horizontal='center')
    for (addr, val) in values.items():
        if addr == 'alignments':
            continue
        sheet[addr] = val
    save()


def max_row(sheet: str) -> int:
    global book
    return book[sheet].max_row


def column(sheet: str, col: int) -> list:
    global book
    sheet = book[sheet]
    return [cell.value for cell in tuple(sheet.columns)[col][1:]]


def sheet(sheet: str) -> [dict]:
    """
    获取某个表的所有数据
    :param sheet: 表
    :return: 数据
    """
    global book
    sheet = book[sheet]
    title = [cell.value for cell in tuple(sheet.rows)[0]]

    def make_data(index, row):
        val = [cell.value for cell in row]
        data = dict(zip(title, val))
        data['_val'] = val
        data['_row'] = index
        return data

    return [make_data(index, row) for (index, row) in enumerate(tuple(sheet.rows)[1:], 1)]


def save():
    global book
    book.save('data/netshop.xlsx')
    book.close()
    book = openpyxl.load_workbook('data/netshop.xlsx')
